* This code constructs a dataset with changes in: Mexican and US tariffs, Mexican input tariffs,
* an index of exposure to US tariffs faced by Mexican producers, and Mexican imported input shares.
* Changes are computed between 199x and 1999. See paper's Appendix for additional details.

*  Mexican tariffs to US imports at the 2-digit Rev 3 leve
set more off
import delimited "Database/WITS/input/MEXfromUSACANisicR3.csv", clear
keep partnername product productname tariffyear dutytype weightedaverage importsvaluein1000usd
ren tariffyear year
keep if partnername=="United States"
keep if dutytype=="AHS"
drop dutytype
local t0 = 1991 // No data for 1993 in WITS
keep if year==`t0' | year==1999
rename weightedaverage weighted
gen dum=0
replace dum=1 if year==`t0'
sort product year
foreach x of var weighted importsvaluein1000usd {
gen `x'`t0'_temp = `x' * dum
bys product: egen `x'`t0' = total(`x'`t0'_temp)
drop `x'`t0'_temp 
}
drop dum
ren importsvaluein1000usd`t0' value`t0'
drop if year==`t0'
drop year
gen dweighted = weighted - weighted`t0' // in percentage points
drop weighted* importsvaluein1000usd

* Collapse to the OECD sector groupings of the ISIC Rev. 3 sectors
qui do "Database/WITS/input/OECD sector grouping.do"
bys sector: egen totalvalue = total(value`t0')
gen value_share =value`t0'/totalvalue

*compute import value weighted average at the OECD grouping sector level
foreach x of var dweighted {
gen temp`x' = `x' * value_share 
bys sector: egen `x'_2 = total(temp`x')
}
collapse dweighted_2, by(sector)
ren dweighted_2 dweighted
sort sector
save "Database/WITS/temp/dtariffs_MexToUS.dta", replace // Mexican tariffs applied ot US

* US tariffs to Mexican Imports
* Compute US tariffs applied to Mexico at the 2-digit Rev 3 with OECD sector grouping

import delimited "Database/WITS/input/USACANISICR3.csv", clear

keep reportername product productname tariffyear dutytype weightedaverage importsvaluein1000usd
ren tariffyear year
keep if reportername=="United States"
keep if dutytype=="AHS"
drop dutytype
local t0 = 1993
keep if year==`t0' | year==1999
rename weightedaverage weighted
gen dum=0
replace dum=1 if year==`t0'
sort product year
foreach x of var weighted importsvaluein1000usd {
gen `x'`t0'_temp = `x' * dum
bys product: egen `x'`t0' = total(`x'`t0'_temp)
drop `x'`t0'_temp 
}
drop dum importsvaluein1000usd
ren importsvaluein1000usd`t0' value`t0'
drop if year==`t0'
drop year
gen dweighted = weighted - weighted`t0' // in percentage points
drop weighted* 

* Collapse to OECD sector groupings:
qui do "Database/WITS/input/OECD sector grouping.do"
bys sector: egen totalvalue = total(value`t0')
gen value_share =value`t0'/totalvalue

* Compute import value weighted average at the OECD grouping sector level
foreach x of var dweighted {
gen temp`x' = `x' * value_share 
bys sector: egen `x'_2 = total(temp`x')
}

collapse dweighted_2, by(sector)
ren dweighted_2 dweighted_usa
sort sector
save "Database/WITS/temp/dtariffs_USToMex.dta", replace // US tariffs applied to Mex

* Input-output table for Mexico in 1995
* A. Compute expenditure share of each buyer sector in each domestic seller sector, for 1995

import delimited using "Database/OECD/input/MEX1995domimp", clear
drop cons gfcf invnt cons_nonres cons_abr expo gdp
gen var = _n
drop if var>68 // drop some unnecessary categories (last rows)
local jj =1
local sectors "c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95"
foreach cc of local sectors  {
egen purch`jj' = total(`cc')
gen share`jj' = `cc' / purch`jj' // share out of total expenditure including domestic and imported inputs
egen purch_impo`jj' = total(`cc') if var>=35
gen share_impo`jj' = `cc' / purch_impo`jj' // share out of total import expenditure
local jj = `jj'+1
}
drop if var<35 // focus on imported inputs
gen sector_dest = _n
drop c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95
drop purch* purch_impo*
drop var
order sector_dest, first
reshape long share share_impo, i(sector_dest) j(sector_origin)
order sector_origin, first // sector_origin is the buyer, sector_destination is the seller; below we add tariffs for destination sectors
sort sector_origin sector_dest 
rename sector_dest sector
sort sector
save "Database/OECD/temp/IOtemp.dta", replace
preserve
keep if sector_origin==1
rename v1 sector_name
keep sector sector_name
save "Database/OECD/temp/sectornames.dta", replace
restore

* B. Compute share of sales to each sector by each domestic sector, for 1995
import delimited using "Database/OECD/input/MEX1995domimp", clear
drop cons gfcf invnt cons_nonres cons_abr expo gdp
gen var = _n
drop if var>68 // drop unnecessary categories
egen expenditure = rowtotal(c*)
order var, first
keep if var<=34
local jj =1
local sectors "c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95"
foreach cc of local sectors  {
gen share`jj' = `cc' / expenditure // share out of total sales 
local jj = `jj'+1
}

drop c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95
drop expenditure
ren var sector_seller
reshape long share, i(sector_seller) j(sector)
sort sector_seller sector
sort sector
sort sector
save "Database/OECD/temp/IOtempSeller.dta", replace

* C. Sector-level shares in total Manufacturing VA, for 1994

import delimited using "Database/OECD/input/MEX1995domimp", clear
drop cons gfcf invnt cons_nonres cons_abr expo 
keep if v1 =="VALU"
* create copy of each column with name that works for reshape below
local jj =1
local sectors "c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95 gdp"
* gdp is number 35
foreach cc of local sectors  {
gen VA`jj' = `cc' 
local jj = `jj'+1
}

drop gdp c01t05 c10t14 c15t16 c17t19 c20 c21t22 c23 c24 c25 c26 c27 c28 c29 c30t33x c31 c34 c35 c36t37 c40t41 c45 c50t52 c55 c60t63 c64 c65t67 c70 c71 c72 c73t74 c75 c80 c85 c90t93 c95

reshape long VA, i(v1) j(sector)
drop v1 
drop if sector==35 // discard GDP
egen totalVA = total(VA)
gen VAshare = VA / totalVA
keep sector VAshare
sort sector
save "Database/OECD/temp/VAshares.dta", replace

* Index of input tariff growth in Mexico in 1995 

use "Database/WITS/temp/dtariffs_MexToUS.dta", clear
merge 1:m sector using "Database/OECD/temp/IOtemp.dta"
keep if _merge==3
drop _merge
sort sector_origin sector

* Use weights given by shares out of import spending only
foreach x of var dweighted {
gen temp2`x' = share_impo * `x'
bys sector_origin: egen D2`x' = total(temp2`x') 
replace D2`x'=. if temp2`x'==. // OECD sector 34 has no data in IO tables
}

ren D2dweighted dt2 // weighted average of changes in input tariffs, in p.p. (0-100)

bys sector_origin: gen counter=_n
keep if counter==1
drop sector counter temp* temp2* dweighted share share_impo
rename sector_origin sector
drop v1
save "Database/temp/temporary.dta", replace
merge m:1 sector using "Database/OECD/temp/sectornames.dta"
drop _merge
sort sector

* Add US tariffs to Mexico 

merge 1:1 sector using "Database/WITS/temp/dtariffs_USToMex.dta"
drop _merge
save "Database/temp/buyertariffs.dta", replace

* Index of exposure to US Tariff Changes 

use "Database/WITS/temp/dtariffs_USToMex.dta", clear
merge 1:m sector using "Database/OECD/temp/IOtempSeller.dta"
keep if _merge==3
drop _merge
sort sector_seller sector
gen temp = share * dweighted_usa
bys sector_seller: egen dt_io = total(temp) // weighted average of changes in tariffs of your buyers, in p.p. (0-100)
replace dt_io=. if temp==. // sectors 32 and 34 have no data in OECD I/O table
bys sector_seller: gen counter=_n
keep if counter==1
drop sector counter temp* dweighted_usa share 
rename sector_seller sector
drop v1
save "Database/temp/temporarySeller.dta", replace
merge 1:1 sector using "Database/temp/buyertariffs.dta"
drop _merge
save "Database/temp/buyersellertariffs.dta", replace

* Add sector-level Mexican imported input shares

use "Database/OECD/output/IMPORTSHARES_OECD_inputs_allcountries_sector.dta", clear // here shares are fraction between 0-1
keep if ccode=="MEX"
drop ccode
gen dum=0
replace dum=1 if year==1995
gen importshare1995_temp = importshare * dum
bys sector: egen importshare1995 = total(importshare1995_temp)
replace importshare1995=. if importshare1995_temp==.
drop importshare1995_temp dum mi
keep if year==1999
gen deltasi = (importshare - importshare1995) * 100 // in percentage points
gen growthsi = deltasi/importshare1995
drop importshare importshare1995
save "Database/temp/importshares.dta", replace
merge 1:1 sector using "Database/temp/buyersellertariffs.dta"
keep if _merge==3
drop _merge
save "Database/temp/temp1.dta", replace

* Get OECD sector group descriptions
preserve
clear
import excel using "Database/OECD/input/sectornames.xlsx", first
rename IndustryCode sector_name2
gen sector=_n
sort sector
save "Database/OECD/temp/sectornames.dta",replace
restore
sort sector_name
merge m:m sector using "Database/OECD/temp/sectornames.dta"
drop _merge
sort growthsi
order sector sector_name Description
drop sector_name
ren sector_name2 sector_name
sort sector
ren dweighted_usa dt_usa

* Bring short names for graphs
qui do "Database/OECD/CreateDescription.do"

* Add share in tota value added of each sector
merge 1:1 sector using "Database/OECD/temp/VAshares.dta"
drop _merge

* Add Mexican tariff changes
merge 1:1 sector using "Database/WITS/temp/dtariffs_MexToUS.dta"
drop _merge
ren dweighted dt_mex

* Label variables
qui do "Database/NAFTA/input/CreateLabels.do"

* Save dataset
save "Database/output/Database_NAFTA.dta", replace
